create proc [dbo].[asi_GroupMainDelete]
       @groupKey uniqueidentifier As
begin
    if not exists(select 1 from GroupMember where GroupKey = @groupKey) and
       not exists(select 1 from AccessItem where GroupKey = @groupKey)
    begin
        begin transaction
        delete GroupMemberDetail where GroupKey = @groupKey
        delete GroupMember where GroupKey = @groupKey
        delete AccessItem where GroupKey = @groupKey
        delete GroupMain where GroupKey = @groupKey
        commit transaction
    end
    else
    begin
        RAISERROR('Cannot delete group while child items exist.', 16, 1)
    end
end

GO